package com.ultrasoft.farmasoft.repository;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.ultrasoft.farmasoft.domain.Product;

@Repository(value = "productDao")
@Transactional(readOnly = false)
public class ProductDaoImpl implements ProductDao {
	
	private EntityManager em = null;
	@PersistenceContext
	public void setEntityManager(EntityManager em) {
		this.em = em;
	} 
	
	public List<Product> getListProduct() {
		TypedQuery<Product> q = em.createNamedQuery("Product.findAll",Product.class);
    	return q.getResultList();
	}
	
	public List<Product> getListProduct(boolean active) {
		TypedQuery<Product> q = em.createNamedQuery("Product.findByActive",Product.class);
    	q.setParameter("active",active);
    	return q.getResultList();
	}
	
	public List<Product> getListProductByActiveSubstanceIdByActive(Integer activeSubstanceId,boolean active) {
		TypedQuery<Product> q = em.createNamedQuery("Product.findByActiveSubstanceIdByActive",Product.class);
    	q.setParameter("active",active);
    	q.setParameter("activeSubstanceId",activeSubstanceId);
    	return q.getResultList();
	}
	
	public List<Product> getListProductByActiveSubstanceId(Integer activeSubstanceId) {
		TypedQuery<Product> q = em.createNamedQuery("Product.findByActiveSubstanceId",Product.class);
    	q.setParameter("activeSubstanceId",activeSubstanceId);
    	return q.getResultList();
	}
	
	public List<Product> getListProductByActiveByName(boolean active,String name) {
		TypedQuery<Product> q = em.createNamedQuery("Product.findByActiveByName",Product.class);
    	q.setParameter("name",name+ "%");
    	q.setParameter("active",active);
    	return q.getResultList();
	}
	
	public List<Product> getListProductByActiveByCode(boolean active,String code) {
		TypedQuery<Product> q = em.createNamedQuery("Product.findByActiveByCode",Product.class);
    	q.setParameter("code",code+ "%");
    	q.setParameter("active",active);
    	return q.getResultList();
	}
	
	public List<Product> getListProductByActiveByCodeBar(boolean active,String codeBar) {
		TypedQuery<Product> q = em.createNamedQuery("Product.findByActiveByCodeBar",Product.class);
    	q.setParameter("codeBar",codeBar+ "%");
    	q.setParameter("active",active);
    	return q.getResultList();
	}
	
	
	
	public List<Product> getListProductByproviderIdByActiveByName(Integer providerId,boolean active,String name) {
		TypedQuery<Product> q = em.createNamedQuery("Product.findByProviderIdByActiveByName",Product.class);
    	q.setParameter("name",name+ "%");
    	q.setParameter("providerId",providerId);
    	q.setParameter("active",active);
    	return q.getResultList();
	}
	
	public List<Product> getListProductByproviderIdByActiveByCode(Integer providerId,boolean active,String code) {
		TypedQuery<Product> q = em.createNamedQuery("Product.findByProviderIdByActiveByCode",Product.class);
    	q.setParameter("code",code+ "%");
    	q.setParameter("providerId",providerId);
    	q.setParameter("active",active);
    	return q.getResultList();
	}
	public List<Product> getListProductByproviderIdByActiveByCodeBar(Integer providerId,boolean active,String codeBar) {
		TypedQuery<Product> q = em.createNamedQuery("Product.findByProviderIdByActiveByCodeBar",Product.class);
    	q.setParameter("codeBar",codeBar+ "%");
    	q.setParameter("providerId",providerId);
    	q.setParameter("active",active);
    	return q.getResultList();
	}
	public List<Product> getListProductByproviderId(Integer providerId) {
		TypedQuery<Product> q = em.createNamedQuery("Product.findByProviderId",Product.class);
    	q.setParameter("providerId",providerId);
    	return q.getResultList();
	}
	
	public List<Product> getListProductByproviderId(Integer providerId,boolean active) {
		TypedQuery<Product> q = em.createNamedQuery("Product.findByProviderIdByActive",Product.class);
    	q.setParameter("providerId",providerId);
    	q.setParameter("active",active);
    	return q.getResultList();
	}
	
	public List<Product> getListProductByproductId(Integer productId) {
		TypedQuery<Product> q = em.createNamedQuery("Product.findByProductId",Product.class);
    	q.setParameter("productId",productId);
    	return q.getResultList();
	}
	
	public List<Product> getListProductByproductId(Integer productId,boolean active) {
		TypedQuery<Product> q = em.createNamedQuery("Product.findByProductIdByActive",Product.class);
    	q.setParameter("productId",productId);
    	q.setParameter("active",active);
    	return q.getResultList();
	}
	
	public Product findByProviderIdByProductId(Integer providerId,Integer productId) {
		Product res = null;
    	Query  q = em.createNamedQuery("Product.findByProductIdByProviderId");
    	q.setParameter("providerId",providerId);
    	q.setParameter("productId",productId);
    	if(q.getResultList().size()==1)
    		res=(Product)q.getSingleResult();
    	return res;
	}
	
	public void update(Product product) {
		em.merge(product);
	}
	
	public void add(Product product) {
		em.persist(product);
	}
	
	public Integer findByProductIdMax(){
		Integer res=0;
		if(!this.getListProduct().isEmpty()){
			try {
				res=(Integer)em.createNamedQuery("Product.findByProductIdMax").getSingleResult();
			} catch (Exception e) {}
		}
		return res;
	}
	 
	@SuppressWarnings("unchecked")
	public List<Object[]> getListObjectProductByStatePackageByStateItem() {
		Query q = em.createQuery("SELECT pp.product.productPK.providerId,pp.product.productPK.productId,pp.product.provider.name,pp.product.name,pp.product.activeSubstanceId.name,pp.repositionLevel,count(*) "
				+ "FROM Pharmacyproduct pp INNER JOIN pp.product.activeSubstanceId acsus ,"
				+ "Package pa INNER JOIN pa.pharmacyproduct ppPack ,"
				+ "Item i INNER JOIN i.package1 packItem "
				+ "WHERE pa.state = :statePack "
				+ "AND packItem.packageId = pa.packageId "
				+ "AND pp.pharmacyproductPK.pharmacyId = ppPack.pharmacyproductPK.pharmacyId "
				+ "AND pp.pharmacyproductPK.productId = ppPack.pharmacyproductPK.productId "
				+ "AND pp.pharmacyproductPK.providerId = ppPack.pharmacyproductPK.providerId "
				+ "AND i.state = :stateItem "
				+ "GROUP BY pp.product.productPK.providerId,pp.product.productPK.productId "//);
				+ "having count(*) <= (pp.repositionLevel+5) "
				+ "order by pp.product.provider.name,pp.product.name ");		
		q.setParameter("statePack",(short)1);
		//q.setParameter("limitRepositionLevel",(long)1);
		q.setParameter("stateItem",(short)1);
		return q.getResultList();
	}
}
